﻿using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

namespace ncp.Business
{
    public class UserInfoBll
    {
        /// <summary>
        /// 得到用户信息表
        /// </summary>
        public static DataTable GetUserInfoTable(string p_Where, string p_Orderby, string pDataBase)
        {
            string sqlStr = "select * from sa_user";
            if (!string.IsNullOrEmpty(p_Where))
                sqlStr += " where " + p_Where;
            if (!string.IsNullOrEmpty(p_Orderby))
                sqlStr += " order by " + p_Orderby;
            return CommonBll.GetdtList(sqlStr, pDataBase);
        }

        /// <summary>
        /// 得到记录数
        /// </summary>
        public static int GetUserInfoCount(string pcWhere, string pDataBase)
        {
            string sqlStr = "select count(*) from sa_user";
            if (!string.IsNullOrEmpty(pcWhere))
                sqlStr += " where " + pcWhere;
            return CommonBll.ReadInteger(sqlStr, pDataBase);
        }


        /// <summary>
        /// 用户是否存在
        /// </summary>
        public static int IsExistUser(string User_Account, string User_id, string pDataBase)
        {
            string sqlStr = "select count(1) from sa_user where User_Account='" + User_Account + "' and User_id<>'" + User_id + "'";
            return CommonBll.ReadInteger(sqlStr, pDataBase);
        }

        /// <summary>
        /// 修改用户密码
        /// </summary>
        public static int UpdateUserPassword(string User_Id, string User_Password, string pDataBase)
        {
            string sqlStr = "update sa_user set User_Password='" + EncryptHelper.EncryptString(User_Password) + "',Expiration_Date= getdate() where User_Id='" + User_Id + "'";
            return CommonBll.ExecuteNonQuery(sqlStr, pDataBase);
        }

        /// <summary>
        /// 用户登录
        /// </summary>
        /// <param name="account"></param>
        /// <param name="password"></param>
        /// <param name="pDataBase"> </param>
        /// <returns></returns>
        public static DataTable UserLogin(string account, string password, string pDataBase)
        {
            string sqlStr = "select * from sa_user where User_Account=@account and User_Password=@password and User_Status=1";
            SqlParameter[] paras = new SqlParameter[]
                                       {
                                           new SqlParameter("@account", account),
                                           new SqlParameter("@password", password)
                                       };
            return CommonBll.GetdtList(sqlStr, pDataBase, paras);
        }

        /// <summary>
        /// 修改用户用户状态
        /// </summary>
        public static int UpdateUserStatus(string User_Id, string User_Status, string pDataBase)
        {
            string sqlStr = "update sa_user set User_Status='" + User_Status + "' where User_Id='" + User_Id + "'";
            return CommonBll.ExecuteNonQuery(sqlStr, pDataBase);
        }

        /// <summary>
        /// 修改用户信息
        /// </summary>
        public static int UpdateUserInfo(Dictionary<string, object> dicUser, string pDataBase)
        {
            string sqlStr = "UPDATE dbo.sa_user " +
                            "SET User_Name = '" + dicUser["user_name"].ToString() + "',	" +
                            "User_Account = '" + dicUser["user_account"].ToString() + "', " +
                            "User_Email = '" + dicUser["user_email"].ToString() + "', " +
                            "User_Mobile =  '" + dicUser["user_mobile"].ToString() + "', " +
                            "User_Status =  '" + dicUser["user_status"].ToString() + "', " +
                            "User_Role =  '" + dicUser["user_role"].ToString() + "' " +
                            "where User_id='" + dicUser["user_id"].ToString() + "'";
            return CommonBll.ExecuteNonQuery(sqlStr, pDataBase);
        }

        /// <summary>
        /// 增加用户信息
        /// </summary>
        public static int InsertUserInfo(Dictionary<string, object> dicUser, string pDataBase)
        {
            string sqlStr = "INSERT INTO dbo.sa_user (User_Account, User_name, User_Password, User_Email, User_Mobile, User_Role, User_RegsiterDate,Expiration_Date, Expiration_Days, User_State) " +
                            "VALUES ('" + dicUser["user_account"].ToString() + "', '" + dicUser["user_name"].ToString() + "', '" + EncryptHelper.EncryptString(dicUser["user_password"].ToString()) + "', '" + dicUser["user_email"].ToString() + "', '" + dicUser["user_mobile"].ToString() + "', '" + dicUser["user_role"].ToString() + "',getdate(),getdate(), 0, '" + dicUser["user_status"].ToString() + "')";
            return CommonBll.ExecuteQuery(sqlStr, pDataBase);
        }

        /// <summary>
        /// 删除用户
        /// </summary>
        public static int DeleteUserInfo(string User_id, string pDataBase)
        {
            string sqlStr = "if not exists(select 1 from bi_NewsInfo where User_id in (" + User_id + ")) " +
                        "delete from sa_user where User_id in(" + User_id + ")";
            return CommonBll.ExecuteNonQuery(sqlStr, pDataBase);
        }
    }
}
